package Product;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import entity.Product;

public class ProductDao {

    private static final String URL = "jdbc:mysql://localhost:3306/taobao";
    private static final String USER = "root";
    private static final String PASSWORD = "20040611";

    private ArrayList<HashMap<String, String>> datas = new ArrayList<HashMap<String, String>>();

    public ArrayList<HashMap<String, String>> connectToDatabase() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {
            // 执行一个简单的查询
            ResultSet rs = stmt.executeQuery("SELECT * FROM products");
            while (rs.next()) {
                HashMap<String, String> row = new HashMap<>();
                row.put("pid", String.valueOf(rs.getInt("PRODUCT_ID")));
                row.put("uid", String.valueOf(rs.getInt("USER_ID")));
                row.put("name", rs.getString("PRODUCT_NAME"));
                row.put("description", rs.getString("PRODUCT_DESCRIPTION"));
                row.put("price", String.valueOf(rs.getDouble("PRICE")));
                row.put("image", rs.getString("IMAGE"));
                row.put("total", String.valueOf(rs.getInt("stock_quantity")));
                datas.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return datas;
    }

    public HashMap<String, String> getByPid(int pid) {
        HashMap<String, String> row = new HashMap<>();
        String query = "SELECT * FROM products WHERE PRODUCT_ID=?"; // SQL查询语句
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setInt(1, pid); // 设置pid参数
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) { // 如果查询到结果
                    row.put("pid", String.valueOf(rs.getInt("PRODUCT_ID")));
                    row.put("uid", String.valueOf(rs.getInt("USER_ID")));
                    row.put("name", rs.getString("PRODUCT_NAME"));
                    row.put("description", rs.getString("PRODUCT_DESCRIPTION"));
                    row.put("price", String.valueOf(rs.getDouble("PRICE")));
                    row.put("image", rs.getString("IMAGE"));
                    row.put("total", String.valueOf(rs.getInt("stock_quantity")));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    public void addFavorite(int userId, int productId) {
        String query = "INSERT INTO product_favorites (USER_ID, PRODUCT_ID) VALUES (?, ?)";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setInt(1, userId);
            pstmt.setInt(2, productId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<HashMap<String, String>> getFavoritesByUserId(int userId) throws SQLException {
        List<HashMap<String, String>> favorites = new ArrayList<>();
        String query = "SELECT pf.PRODUCT_ID, pf.USER_ID, pf.PRODUCT_NAME, pf.PRODUCT_DESCRIPTION, pf.PRICE, pf.IMAGE, pf.STOCK_QUANTITY " +
                "FROM products pf " +
                "JOIN product_favorites p ON pf.PRODUCT_ID = p.PRODUCT_ID " +
                "WHERE p.USER_ID = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setInt(1, userId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    HashMap<String, String> row = new HashMap<>();
                    row.put("pid", String.valueOf(rs.getInt("PRODUCT_ID")));
                    row.put("uid", String.valueOf(rs.getInt("USER_ID")));
                    row.put("name", rs.getString("PRODUCT_NAME"));
                    row.put("description", rs.getString("PRODUCT_DESCRIPTION"));
                    row.put("price", String.valueOf(rs.getDouble("PRICE")));
                    row.put("image", rs.getString("IMAGE"));
                    row.put("total", String.valueOf(rs.getInt("STOCK_QUANTITY")));
                    favorites.add(row);
                }
                return favorites;
            }
        }
    }

    public void addProduct(Product product) {
        String query = "INSERT INTO products (USER_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, PRICE, IMAGE, STOCK_QUANTITY) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setInt(1, product.getUserId());
            pstmt.setString(2, product.getProductName());
            pstmt.setString(3, product.getProductDescription());
            pstmt.setDouble(4, product.getPrice());
            pstmt.setString(5, product.getImage());
            pstmt.setInt(6, product.getStockQuantity());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void deleteProduct(int productId) {
        String query = "DELETE FROM products WHERE PRODUCT_ID = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setInt(1, productId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public boolean isFavorite(int userId, int productId) {
        String query = "SELECT COUNT(*) FROM product_favorites WHERE USER_ID = ? AND PRODUCT_ID = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setInt(1, userId);
            pstmt.setInt(2, productId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public void removeFavorite(int userId, int productId) {
        String query = "DELETE FROM product_favorites where USER_ID = ? AND PRODUCT_ID = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            pstmt.setInt(1, userId);
            pstmt.setInt(2, productId);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public ArrayList<HashMap<String, String>> searchProductsByName(String name) {
        ArrayList<HashMap<String, String>> products = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM products WHERE PRODUCT_NAME LIKE ?")) {
            pstmt.setString(1, "%" + name + "%");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                HashMap<String, String> product = new HashMap<>();
                product.put("pid", String.valueOf(rs.getInt("PRODUCT_ID")));
                product.put("uid", String.valueOf(rs.getInt("USER_ID")));
                product.put("name", rs.getString("PRODUCT_NAME"));
                product.put("description", rs.getString("PRODUCT_DESCRIPTION"));
                product.put("price", String.valueOf(rs.getDouble("PRICE")));
                product.put("image", rs.getString("IMAGE"));
                product.put("total", String.valueOf(rs.getInt("stock_quantity")));
                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return products;
    }
}
